MySQL - Command - ii Database Management System - Java, Java Swing, OOAD, MIS, DSA

Data Base

MySQL - Command - ii

DROP TABLE users;

To view all databases created in MySQL server:

SHOW DATABASES;

To create a new database with name mydb:

CREATE DATABASE mydb;

To Select or Use a Database named mydb:

USE mydb;

We must first use use command to work on a database. Only after running use command, we can create new tables in database, view tables.

To view all Tables in Database

SHOW TABLES;

Create a Table:

            CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100)
);

Insert Data:

INSERT INTO users (name, email) VALUES ('Ram', 'ram@example.com');

here, it is not necessary to write statements in capital letters.

View Data

SELECT * FROM users;

Update Data

UPDATE users SET name='Ram Kumar' WHERE id=1;

Delete Data

DELETE FROM users WHERE id=1;

To remove table from database:

Delete a Database: Database must exist.

DROP DATABASE mydb;

Add a New Column to existing table:

ALTER TABLE users ADD COLUMN age INT;

Modify (change data type)an Existing Column:

ALTER TABLE users MODIFY COLUMN email VARCHAR(200);

Rename a Column, that is change name of column with new name:

RENAME TABLE users TO customers;

Add Unique Constraint to existing table:

ALTER TABLE users ADD UNIQUE (email);

This command will make email attribute unique for users table. That means, users table can not have duplicate emails.

Remove Unique Constraint from existing column.

ALTER TABLE users DROP INDEX email;

This command will remove unique constraint from attribute email.

Create an Index: Index makes search fast.

CREATE INDEX idx_name ON users(name);

Delete an Index from existing attribute having index:

DROP INDEX idx_name ON users;

Filtering Records: That is displaying records from that based on specified conditions: here records of only those users will be display whose age is greater than 18.

SELECT * FROM users WHERE age > 18;

Sorting Records:

SELECT * FROM users ORDER BY name ASC;

Here, users records will be displayed sorted by name in ascending order.

SELECT * FROM users ORDER BY age DESC;

Here, users records will be displayed sorted by name in descending order.

Limit Number of Results: displaying only specified number of records form table or tables.

SELECT * FROM users LIMIT 5;

Only five records will be displayed.

Count Records: Display number of records in a given table (relation).

SELECT COUNT(*) FROM users;

Grouping Data: That is grouping records of table (relation) according to specified attributes.

SELECT age, COUNT(*) FROM users GROUP BY age;

Here, records of users table will be group by age first, then count records for each age, and display table with values of distinct age and the number of records per age.

Using LIKE for Searching: We can provide part of string to search using LIKE keyword.

SELECT * FROM users WHERE name LIKE '%ram%';

Here, users with name having ram anywhere in their name will be displayed.

JOIN Between Tables: Two tables can be joined based on similar attributes which both table have.

SELECT users.name, orders.amount FROM users JOIN orders ON users.id = orders.user_id;

Here, tables: users and orders both have similar attribute (both attributes have same datatypes) id and user_id.

Online-Academy

Look, Read, Understand, Apply

Data Base